﻿using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YunSpace.Core;
using YunSpace.Data;
using YunSpace.Domain.Entity.SiteMgr;
using YunSpace.Domain.IRepository.SiteMgr;
using YunSpace.Domain.ViewModel.Sale;
using YunSpace.Domain.ViewModel.SiteMgr;
using YunSpace.Domain.ViewModel.SiteMgr.SalesTarget;
using YunSpace.Domain.ViewModel.SiteMgr.SalesTarget;


namespace YunSpace.Repository.SiteMgr
{
    public class SiteSalesTargetSettingRepository : RepositoryBase<SiteSalesTargetSettingEntity>, ISiteSalesTargetSettingRepository
    {
        /// <summary>
        /// 获取月指标列表数据
        /// </summary>
        /// <returns></returns>
        public List<MonthTargetGridDto> GetMonthTargetGridDtos(Pagination pagination, GetMonthTargetGridRequest request)
        {
            var departmentIdFilterSql = (request.DepartmentId == null ? "" : $" AND s.F_DepartId = '{request.DepartmentId}'");
            var userIdFilterSql = (request.UserId == null ? "" : $" AND s.F_UserId = '{request.UserId}'");


            var sql = $@"SELECT  u.F_RealName AS Seller ,  
                                 s.F_UserId AS Uid,  
                                MAX(CASE s.F_Month
                                      WHEN '1' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M1' ,
                                MAX(CASE s.F_Month
                                      WHEN '2' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M2' ,
                                MAX(CASE s.F_Month
                                      WHEN '3' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M3' ,
                                MAX(CASE s.F_Month
                                      WHEN '4' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M4' ,
                                MAX(CASE s.F_Month
                                      WHEN '5' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M5' ,
                                MAX(CASE s.F_Month
                                      WHEN '6' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M6' ,
                                MAX(CASE s.F_Month
                                      WHEN '7' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M7' ,
                                MAX(CASE s.F_Month
                                      WHEN '8' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M8' ,
                                MAX(CASE s.F_Month
                                      WHEN '9' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M9' ,
                                MAX(CASE s.F_Month
                                      WHEN '10' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M10' ,
                                MAX(CASE s.F_Month
                                      WHEN '11' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M11' ,
                                MAX(CASE s.F_Month
                                      WHEN '12' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M12',
                                MAX(CASE s.F_Month
                                      WHEN '13' THEN s.F_IndicatorValue
                                      ELSE 0
                                    END) 'M13'
                        FROM    Site_Sales_TargetSetting AS s
                                INNER JOIN Sys_User AS u ON u.F_Id = s.F_UserId
                        WHERE   (s.F_DeleteMark IS NULL || s.F_DeleteMark = 0) AND s.F_Year = {request.Year} {departmentIdFilterSql} {userIdFilterSql}
                        GROUP BY s.F_UserId";

            var startIndex = pagination.rows * (pagination.page - 1);
            var endIndex = startIndex + pagination.rows;

            sql = $"{sql} ORDER BY s.{pagination.sidx} LIMIT {startIndex},{endIndex};";
            pagination.records = GetMonthTargetGridCount(pagination, request.Year, departmentIdFilterSql + userIdFilterSql);

            return FindList<MonthTargetGridDto>(sql);
        }

        /// <summary>
        /// 获取汇总的目标额
        /// </summary>
        /// <returns></returns>
        public decimal GetThresholdsBy(GetMonthTargetGridRequest request)
        {
            var departmentIdFilterSql =
                request.DepartmentId == null ? "" : $" AND s.F_DepartId = '{request.DepartmentId}'";
            var userIdFilterSql = request.UserId == null ? "" : $" AND s.F_UserId = '{request.UserId}'";
            var monthFilterSql = request.Month == null ? $" AND s.F_Month!=13 " : $" AND s.F_Month = {request.Month}";

            var sql = $@"SELECT IFNULL(SUM(s.F_IndicatorValue), 0) 
                        FROM	Site_Sales_TargetSetting AS s
                        WHERE (s.F_DeleteMark IS NULL || s.F_DeleteMark = 0) AND s.F_Year = {request.Year} {monthFilterSql} {departmentIdFilterSql} {userIdFilterSql};";

            return Single<decimal>(sql);
        }

        #region 目标查看

        /// <summary>
        /// 获取目标查看列表
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<LookTargetDto> GetLookTargetDtos(Pagination pagination, GetLookTargetGridJsonRequest request)
        {
            var departmentIdFilterSql = (request.DepartmentId.IsNullOrEmpty() ? "" : $" AND u.F_DepartmentId = '{request.DepartmentId}'");
            var userIdFilterSql = (request.UserId.IsNullOrEmpty() ? "" : $" AND u.F_Id = '{request.UserId}'");
            var beginDate = $"{request.Year}{(request.Month < 10 ? "0" : "")}{request.Month}01";
            var endDate = $"{request.Year}{(request.Month + 1 < 10 ? "0" : "")}{request.Month + 1}01";

            var sql = $@"SELECT 
                                u.F_Id AS UserId ,
                                u.F_RealName AS UserName ,
                                IFNULL(SUM(s.F_IndicatorValue), 0) AS ThresholdAmount ,
                                IFNULL(SUM(d.F_TotalPrice), 0) AS SignedAmount ,
                                IFNULL(SUM(p.F_InOutCash), 0) AS ReturnTargetAmount ,
                                IFNULL(SUM(p2.F_InOutCash), 0) AS ReturnedAmount ,
                                COUNT(bd.F_Id) AS DemandCount ,
                                COUNT(bd2.F_Id) AS DealDemandCount
                        FROM    Sys_User AS u 
                                INNER JOIN Sys_Organize AS o ON o.F_Id = u.F_DepartmentId {departmentIdFilterSql} {userIdFilterSql}
                                LEFT JOIN Site_Sales_TargetSetting AS s ON s.F_UserId = u.F_Id
                                                                                   AND s.F_Year = '{request.Year}'
                                                                                   AND s.F_Month = '{request.Month}'
                                LEFT JOIN Site_Client_Order AS o2 ON o2.F_FlowUserId = u.F_Id
                                                                                  AND o2.F_LastModifyTime >= '{beginDate}'
                                                                                  AND o2.F_LastModifyTime < '{endDate}'
                                LEFT JOIN Site_Client_Order AS o3 ON o3.F_Id = o2.F_Id
                                                                                   AND o3.F_State = 9
                                LEFT JOIN Site_Client_OrderPriceDetail AS d  ON d.F_OrderId = o2.F_Id
                                LEFT JOIN Site_Client_OrderUsingPrice AS p  ON p.F_OrderId = o.F_Id
                                LEFT JOIN Site_Client_OrderUsingPrice AS p2  ON p2.F_Id = p.F_Id
                                                                                      AND p2.F_InOutType = 1
                                LEFT JOIN Site_Clients_ProjectByDemand AS bd ON bd.F_Id = o2.F_DemandId
                                                                                 AND o2.F_FlowUserId = u.F_Id
                                                                                 AND bd.F_State = 1
                                LEFT JOIN Site_Clients_ProjectByDemand AS bd2 ON bd2.F_Id = bd.F_Id
                                                                                  AND bd2.F_Id = o3.F_DemandId
                        WHERE   0 = 0
                                AND (o.F_DeleteMark = 0 || o.F_DeleteMark IS NULL)
                                AND (u.F_DeleteMark = 0 || u.F_DeleteMark IS NULL)                      
                        GROUP BY u.F_Id ,
                                u.F_RealName";

            var startIndex = pagination.rows * (pagination.page - 1);
            var endIndex = startIndex + pagination.rows;

            sql = $"{sql} ORDER BY u.{pagination.sidx} LIMIT {startIndex},{endIndex};";
            pagination.records = GetLookTargetDtosCount(departmentIdFilterSql + userIdFilterSql);

            return FindList<LookTargetDto>(sql);
        }

        /// <summary>
        /// 获取目标查看数量
        /// </summary>
        /// <returns></returns>
        private int GetLookTargetDtosCount(string filterSql)
        {
            var sql = $@"SELECT  COUNT(1)
                        FROM    Sys_User AS u 
                                INNER JOIN Sys_Organize AS o ON o.F_Id = u.F_DepartmentId 
                        WHERE   0 = 0
						    AND (o.F_DeleteMark = 0 || o.F_DeleteMark IS NULL)
                            AND (u.F_DeleteMark = 0 || u.F_DeleteMark IS NULL) 
                            {filterSql};";
            return Single<int>(sql);
        }


        #region 废弃

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<TargetAchievingRateOfLookTargetDto> GetTargetAchievingRateOfLookTargetDtos(GetLookTargetGridJsonRequest request)
        {
            var yearMonthSqlFilterString = GetYearMonthSqlFilterString(request.Year, request.Month);
            var departmentIdFilterSql = (request.DepartmentId.IsNullOrEmpty() ? "" : $" AND s.F_DepartId = '{request.DepartmentId}'");
            var userIdFilterSql = (request.UserId.IsNullOrEmpty() ? "" : $" AND s.F_UserId = '{request.UserId}'");

            var sql = $@"SELECT  u.F_Id AS F_UserId ,
                                u.F_RealName AS F_Seller ,
                                SUM(s.F_IndicatorValue) AS F_IndicatorValues,
                                IFNULL(d.F_TotalPrice, 0) AS F_SignedValues
                        FROM    Sys_User AS u
                                INNER JOIN Site_Sales_TargetSetting AS s ON s.F_UserId = u.F_Id
                                LEFT JOIN Site_Client_Order AS o ON o.F_FlowUserId = u.F_Id
                                                                    AND o.F_OrderState = 9
                                LEFT JOIN Site_Client_OrderPriceDetail AS d ON d.F_OrderId = o.F_Id
                        WHERE   1 = 1
                                {departmentIdFilterSql}
                                {userIdFilterSql}
                                AND s.F_Year = {request.Year}
                                AND F_Month = {request.Month}
                                AND o.F_LastModifyTime >= '{yearMonthSqlFilterString.begin}' AND o.F_LastModifyTime < '{yearMonthSqlFilterString.end}'
                        GROUP BY u.F_Id ,
                                u.F_RealName;";

            return FindList<TargetAchievingRateOfLookTargetDto>(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<ReturnedAchievingRateOfLookTargetDto> GetReturnedAchievingRateOfLookTargetDtos(GetLookTargetGridJsonRequest request)
        {
            var yearMonthSqlFilterString = GetYearMonthSqlFilterString(request.Year, request.Month);
            var departmentIdFilterSql = (request.DepartmentId.IsNullOrEmpty() ? "" : $" AND s.F_DepartId = '{request.DepartmentId}'");
            var userIdFilterSql = (request.UserId.IsNullOrEmpty() ? "" : $" AND s.F_UserId = '{request.UserId}'");

            var sql = $@"SELECT  u.F_Id AS F_UserId ,
                                IFNULL(SUM(d.F_TotalPrice), 0) AS F_ReturnTargetValues ,
                                IFNULL(SUM(d2.F_TotalPrice), 0) AS F_ReturnedValues
                        FROM    Sys_User AS u
                                INNER JOIN Site_Sales_TargetSetting AS s ON s.F_UserId = u.F_Id
                                INNER JOIN Site_Client_Order AS o ON o.F_FlowUserId = u.F_Id
                                LEFT JOIN Site_Client_OrderPriceDetail AS d ON d.F_OrderId = o.F_Id
                                LEFT JOIN Site_Client_OrderPriceDetail AS d2 ON d2.F_Id = d.F_Id
                                                                                AND d2.F_State = 1
                        WHERE   1 = 1
                                {departmentIdFilterSql}
                                {userIdFilterSql}
                                AND s.F_Year = {request.Year}
                                AND F_Month = {request.Month}
                                AND o.F_LastModifyTime >= '{yearMonthSqlFilterString.begin}' AND o.F_LastModifyTime < '{yearMonthSqlFilterString.end}'
                        GROUP BY u.F_Id; ";

            return FindList<ReturnedAchievingRateOfLookTargetDto>(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<ConversionRateOfLookTargetDto> GetConversionRateOfLookTargetDtos(GetLookTargetGridJsonRequest request)
        {
            var yearMonthSqlFilterString = GetYearMonthSqlFilterString(request.Year, request.Month);
            var departmentIdFilterSql = (request.DepartmentId.IsNullOrEmpty() ? "" : $" AND s.F_DepartId = '{request.DepartmentId}'");
            var userIdFilterSql = (request.UserId.IsNullOrEmpty() ? "" : $" AND s.F_UserId = '{request.UserId}'");

            var sql = $@"SELECT  u.F_Id AS F_UserId,
                                COUNT(d.F_Id) AS DemandCount ,
                                COUNT(o2.F_Id) AS DealCount
                        FROM    Sys_User AS u
                                INNER JOIN Site_Sales_TargetSetting AS s ON s.F_UserId = u.F_Id
                                INNER JOIN Site_Client_Order AS o ON o.F_FlowUserId = u.F_Id
                                LEFT JOIN Site_Clients_ProjectByDemand AS d ON d.F_Id = o.F_DemandId
                                                                               AND d.F_State = 1
                                LEFT JOIN Site_Client_Order AS o2 ON o2.F_Id = o.F_Id
                                                                     AND o2.F_OrderState = 9
                                                                     AND o2.F_LastModifyTime >= '{yearMonthSqlFilterString.begin}'
                                                                     AND o2.F_LastModifyTime < '{yearMonthSqlFilterString.end}'
                        WHERE   1 = 1
                                {departmentIdFilterSql}
                                {userIdFilterSql}
                                AND s.F_Year = {request.Year}
                                AND F_Month = {request.Month}
                                AND o.F_LastModifyTime >= '{yearMonthSqlFilterString.begin}' AND o.F_LastModifyTime < '{yearMonthSqlFilterString.end}'
                        GROUP BY u.F_Id;";

            return FindList<ConversionRateOfLookTargetDto>(sql);
        }

        #endregion



        #endregion 目标查看

        #region private methods

        /// <summary>
        /// 获取月目标列表数量
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="year"></param>
        /// <param name="filterSql"></param>
        /// <returns></returns>
        private int GetMonthTargetGridCount(Pagination pagination, int year, string filterSql)
        {
            var sql = $@"SELECT COUNT(DISTINCT s.F_UserId)
                                FROM  Site_Sales_TargetSetting AS s
                                WHERE  (s.F_DeleteMark IS NULL || s.F_DeleteMark = 0)   AND s.F_Year = {year} {filterSql};";
            return Count(sql);
        }

        private int GetMonthTargetGridCount(Pagination pagination, int year, string filterSql, double totalIndicatorValue)
        {
            var sql = $@"SELECT COUNT(1)
                        FROM 
                        (
	                        SELECT SUM(s.F_IndicatorValue) AS IndicatorValues
	                        FROM Site_Sales_TargetSetting AS s
	                        WHERE s.F_Year = {year} {filterSql}
	                        GROUP BY s.F_UserId
                        ) AS t
                        WHERE t.IndicatorValues >= {totalIndicatorValue};";
            return Count(sql);
        }

        /// <summary>
        /// 获取年月过滤条件字符串
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        private static dynamic GetYearMonthSqlFilterString(int year, int month)
        {
            return new
            {
                begin = $"{year}{(month < 10 ? "0" : "")}{month}01",
                end = $"{year}{(month + 1 < 10 ? "0" : "")}{month + 1}01"
            };
        }

        private List<TEntity> FindList<TEntity>(string sql) where TEntity : class
        {
            using (var db = new RepositoryBase())
            {
                var result = db.FindList<TEntity>(sql);
                return result;
            }
        }

        #endregion private methods


    }
}
